package cn.changeforyou.analyzer.dbParse;

import cn.changeforyou.analyzer.dbParse.model.MysqlTableInfo;
import cn.changeforyou.analyzer.dbParse.model.MysqlTableInfo.ColumnInfo;
import cn.changeforyou.analyzer.sqlparse.SqlParserUtil;
import cn.changeforyou.utils.string.StringUtils;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/***
 * 优化获取表信息可以利用如下的sql
 * SELECT
 *     TABLE_SCHEMA AS '库名',
 *     TABLE_NAME AS '表名',
 *     COLUMN_NAME AS '列名',
 *     ORDINAL_POSITION AS '列的排列顺序',
 *     COLUMN_DEFAULT AS '默认值',
 *     IS_NULLABLE AS '是否为空',
 *     DATA_TYPE AS '数据类型',
 *     CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
 *     NUMERIC_PRECISION AS '数值精度(最大位数)',
 *     NUMERIC_SCALE AS '小数精度',
 *     COLUMN_TYPE AS 列类型,
 *     COLUMN_KEY 'KEY',
 *     EXTRA AS '额外说明',
 *     COLUMN_COMMENT AS '注释'
 * FROM
 *     information_schema.`COLUMNS`
 * WHERE
 *     TABLE_SCHEMA = 'waimai'
 * ORDER BY
 *     TABLE_NAME,
 *     ORDINAL_POSITION;
 *     
 *     
 * 获取表信息也可以如下: mybatis-plus generator就是用如此方法
 * show full fields from `tms_test`;
 * show table status where 1= 1 and name = 'tms_test';
 * 
 * SHOW CREATE TABLE tms_test; 理论上建表语句可以解析完上面全部, 但是要解析sql吗何必呢
 * @author zhyu
 */
@Slf4j
@Getter
public class DatabaseUtil {

    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    private String url;
    private String username;
    private String password;

    /**
     * 数据库操作
     */
    private final String SQL = "SELECT * FROM ";

    public DatabaseUtil(String url, String username, String password) {
        this.url = url;
        this.username = username;
        this.password = password;
        try {
            Class.forName(DRIVER);
            getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            log.error("can not load jdbc driver", e);
        }
    }

    public String getTableComment(String tableName) {
        String sql = "SHOW CREATE TABLE " + tableName;
        //与数据库的连接
        Statement statement = null;
        String createTableSql = null;
        try {
            Connection conn = getConnection();
            statement = conn.createStatement();
            statement.execute(sql);
            //结果集元数据
            //表列数
            ResultSet resultSet = statement.getResultSet();
            while(resultSet.next()){
                createTableSql = resultSet.getString("Create Table");
            }
            if(StringUtils.isNotEmpty(createTableSql)) {
                return SqlParserUtil.getMysqlTableCommentByCreateTableSql(createTableSql);
            }
        } catch (SQLException e) {
            log.error("getColumnNames failure", e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return null;
    }

    public MysqlTableInfo getMysqlTableInfo(String tableName) {
        List<String> names = getColumnNames(tableName);
        List<String> columnTypes = getColumnTypes(tableName);
        List<String> columnComments = getColumnComments(tableName);
        String tableComment = getTableComment(tableName);
        closeConnection();
        MysqlTableInfo tableInfo = new MysqlTableInfo();
        List<ColumnInfo> columnInfos = new ArrayList<>();
        tableInfo.setTableName(tableName);
        tableInfo.setColumnInfos(columnInfos);
        tableInfo.setTableComment(tableComment);
        for (int i = 0; i < names.size(); i++) {
            ColumnInfo info = new ColumnInfo(tableInfo);
            columnInfos.add(info);
            info.setColumnComment(columnComments.get(i));
            info.setColumnName(names.get(i));
            info.setColumnType(columnTypes.get(i));
        }
        return tableInfo;
    }

    Connection conn;


    /**
     * 关闭数据库连接
     */
    void closeConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("close connection failure", e);
            }
        }
    }


    /**
     * 获取数据库下的所有表名
     */
    public List<String> getTableNames() {
        List<String> tableNames = new ArrayList<>();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            log.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                log.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }

    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @return
     */
    public List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            Connection conn = getConnection();
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnNames failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */
    public List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        try {
            Connection conn = getConnection();
            pStemt = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = pStemt.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            log.error("getColumnTypes failure", e);
        } finally {
            if (pStemt != null) {
                try {
                    pStemt.close();
                } catch (SQLException e) {
                    log.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName
     * @return
     */
    public List<String> getColumnComments(String tableName) {
        //与数据库的连接
        PreparedStatement pStemt = null;
        String tableSql = SQL + tableName;
        //列名注释集合
        List<String> columnComments = new ArrayList<>();
        ResultSet rs = null;
        try {
            Connection conn = getConnection();
            pStemt = conn.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    log.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }

    Connection getConnection() {
        return getConnection(url, username, password);
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    private Connection getConnection(String URL, String USERNAME, String PASSWORD) {
        if (null == conn) {
            synchronized (DatabaseUtil.class) {
                if (null == conn) {
                    try {
                        conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
                    } catch (SQLException e) {
                        log.error("get connection failure", e);
                    }
                }
            }
        }
        return conn;
    }

}